<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1" import="support.*, java.util.*,  java.sql.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
	<link rel="stylesheet" type="text/css" href="styleCSS.css" /> 	
	<link rel="stylesheet" type="text/css" href="columnCSS.css" /> 
	<title>Database updating... </title>
</head>
<body>
	<p class="sansserif">
	<div class="container">
	<div class="center_div">
		<% 
			support s = new support();
			try {
				//Registering Postgresql JDBC driver
				Class.forName("org.postgresql.Driver");
			} catch (ClassNotFoundException e) {
				System.err.println("ClassNotFoundException: " + e);
			} 
			try {	
				//Open a connection to the database
				Connection conn = DriverManager.getConnection(
						"jdbc:postgresql://localhost:5432/CSE135_SP11?"
						+ "user=postgres&password=postgres");
		
				conn.setAutoCommit(false);
				
				/* INSERT to Countries table */
				PreparedStatement pstmt = conn.prepareStatement(
						"INSERT INTO Countries (country_name) VALUES (?)");
	
				String row;
				int rowCount;
			 	String path = config.getServletContext().getRealPath("countries.txt");
				Vector countries = s.getCountries(path);			
	
				
				for(int i = 0; i < countries.size(); i++)
				{
					row = (String) countries.get(i);
					pstmt.setString(1, row);
					rowCount = pstmt.executeUpdate();
				}

				/* INSERT to Locations table */
				pstmt = conn.prepareStatement(
						"INSERT INTO Locations (location) VALUES (?);");
			 	path = config.getServletContext().getRealPath("universities.txt");
				Vector loc = s.getUniversities(path);
				for(int i = 0; i < loc.size(); i++)
				{
					Vector univ_tuple = (Vector)loc.get(i);
					pstmt.setString(1, (String)univ_tuple.get(0));
					rowCount = pstmt.executeUpdate();
				}				
				
				/* INSERT to Universities table */
				int location_id = 0;
				// First: need to get the integer from Locations table
			 	path = config.getServletContext().getRealPath("universities.txt");
				Vector tuples = s.getUniversities(path);
				for(int i = 0; i < tuples.size(); i++)
				{
					pstmt = conn.prepareStatement( 
					"SELECT id FROM Locations WHERE location = ?"); 
					Vector state = (Vector)tuples.get(i);
					String location = (String)state.get(0);
					pstmt.setString(1, location);
					ResultSet rs = pstmt.executeQuery();

					while(rs.next())
						location_id = rs.getInt("id");

					//Next: Insert into University table int, string 
					pstmt = conn.prepareStatement( 
						"INSERT INTO Universities (location, university) VALUES (?, ?);");
					Vector tuple = (Vector)state.get(1);
					for(int j= 0; j < tuple.size(); j++)
					{
						pstmt.setInt(1, location_id);
						pstmt.setString(2, (String)tuple.get(j));
						pstmt.executeUpdate();
					}
				}
	
				/* INSERT to Disciplines table */
				pstmt = conn.prepareStatement(
					"INSERT INTO Disciplines (discipline) VALUES (?);");
			 	path = config.getServletContext().getRealPath("majors.txt");
				Vector majors = s.getMajors(path);
				for(int i = 0; i < majors.size(); i++)
				{
					pstmt.setString(1, (String)majors.get(i));
					rowCount = pstmt.executeUpdate();
				}
	
				/* INSERT to specializations table*/
				pstmt = conn.prepareStatement(
					"INSERT INTO Specializations (specialization) VALUES (?);");
			 	path = config.getServletContext().getRealPath("specializations.txt");
				Vector specs = s.getCountries(path);
				for(int i = 0; i < specs.size(); i++)
				{
					pstmt.setString(1, (String)specs.get(i));
					rowCount = pstmt.executeUpdate();
				}
				
				conn.commit();
				pstmt.close();
				conn.close();
				
			} catch (SQLException e) {
				System.err.println("SQLException: " + e);
			}
	
		%>
		<p> Database update... creating tables... done!</p>
	</div>
	</div>		
</body>
</html>